package cn.wit.sst.utils;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import cn.wit.sst.domain.Raw;

public class DataLoader {
	private static final int STU_NO 		= 0; // 学号
	private static final int STU_NAME 	= 1; // 姓名
	private static final int COLLEGE 		= 2; // 学院
	private static final int CLAZZ 		= 3; // 班级
	private static final int CREDIT		= 4; // 学分
	private static final int COURSE_TYPE	= 5; // 课程性质
	private static final int COURSE_NO 	= 6; // 选课课号
	private static final int COURSE_NAME	= 7; // 课程名称
	private static final int SCORE_ORD	= 8; // 平时成绩
	private static final int SCORE_MID  	= 9; // 期中成绩
	private static final int SCORE_END	= 10; // 期末成绩
	private static final int SCORE_EXP 	= 11; // 实验成绩
	private static final int SCORE_TOTAL	= 12; // 总评成绩
	private static final int SCORE_AMO	= 13; // 折算成绩
	private static final int SCORE_SUP 	= 14; // 补考成绩
	private static final int RETAKE_MARK 	= 15; // 重修标记
	private static final int SCORE_RET	= 16; // 重修成绩
	private static final int REMARK		= 17; // 备注
	private static final int GRADE		= 18; // 学年
	private static final int SEMESTER		= 19; // 学期
	private static final int GPA			= 20; // 绩点

	private static Logger logger = LoggerFactory.getLogger(DataLoader.class);

	public void test() {
		try {
			loadData("");
		} catch (EncryptedDocumentException e) {
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static List<Raw> loadData(String file) throws EncryptedDocumentException, InvalidFormatException, IOException {
		logger.info("开始读取文件");
		Workbook wb = WorkbookFactory.create(new File(file));	// 打开excel文件
		logger.info("读取完毕。一共{}个sheet。", wb.getNumberOfSheets());

		logMemory();	// 记录内存使用

		List<Raw> data = new ArrayList<>();
		// 1. 遍历每一个sheet
		for (int i = 0; i < wb.getNumberOfSheets(); i++) {
			// TODO 第一个sheet数据格式和其它sheet不一致，先略过
			if (i == 0) continue;

			Sheet sheet = wb.getSheetAt(i);
			int rows = sheet.getPhysicalNumberOfRows();
			logger.info("解析第{} 个sheet：{}, 一共{}行记录", new Object[]{i, wb.getSheetName(i), rows});

			// 2. 遍历sheet里面的每一行 (跳过第一行的表头)
			for (int j = 1; j < rows; j++) {
				Row row = sheet.getRow(j);
				if (row == null) continue;

				// logger.info("解析第{} 行，一共{}列", new Object[] {j, row.getLastCellNum()});

				data.add(parseRow(row));
			}
		}

		return data;
	}

	private static Raw parseRow(Row row) {
		// 3. 遍历每一列
		// for (int k = 0; k < row.getLastCellNum(); k++) {
		Raw raw = new Raw();

		raw.setSno(Long.valueOf(getCellValue(row, STU_NO)));
		raw.setName(getCellValue(row, STU_NAME));
		raw.setCollege(getCellValue(row, COLLEGE));
		raw.setClazz(getCellValue(row, CLAZZ));
		raw.setCredit(Float.valueOf(getCellValue(row, CREDIT)));
		raw.setCourseType(getCellValue(row, COURSE_TYPE));
		raw.setCourseName(getCellValue(row, COURSE_NAME));
		raw.setCourseNo(getCellValue(row, COURSE_NO));
		raw.setScoreOrd(toByte(getCellValue(row, SCORE_ORD)));
		raw.setScoreMid(toByte(getCellValue(row, SCORE_MID)));
		raw.setScoreEnd(toByte(getCellValue(row, SCORE_END)));
		raw.setScoreExp(toByte(getCellValue(row, SCORE_EXP)));

		// 此处分数可能为‘作弊’字符串！
		String scoreTotal = getCellValue(row, SCORE_TOTAL);
		if (isNumeric(scoreTotal)) {
			raw.setScoreTotal(toByte(scoreTotal));
		}

		raw.setScoreAmo(toByte(getCellValue(row, SCORE_AMO)));
		raw.setScoreSup(toByte(getCellValue(row, SCORE_SUP)));

		// 此处分数可能为‘旷考’字符串！
		String scoreRet = getCellValue(row, SCORE_RET);
		if (isNumeric(scoreRet)) {
			raw.setScoreRet(toByte(scoreRet));
		}

		raw.setRetakeMark(getCellValue(row, RETAKE_MARK));
		raw.setRemark(getCellValue(row, REMARK));
		raw.setGrade(getCellValue(row, GRADE));
		raw.setSemester(Byte.valueOf(getCellValue(row, SEMESTER)));
		raw.setGpa(Float.valueOf(getCellValue(row, GPA)));

		return raw;
	}

	private static boolean isNumeric(String str) {
		for (int i = 0; i < str.length(); i++) {
			if (!Character.isDigit(str.charAt(i))) {
				return false;
			}
		}

		return true;
	}

	private static Float toByte(String s) {
		if (s == null || s.length() == 0) {
			return null;
		}

		return Float.valueOf(s);
	}

	private static String getCellValue(Row row, int index) {
		Cell cell = row.getCell(index);
		if (cell == null) return "";

		switch (cell.getCellTypeEnum()) {
			case STRING:
				return cell.getRichStringCellValue().getString();

			case NUMERIC:
				return String.valueOf(cell.getNumericCellValue());

			case BOOLEAN:
				return String.valueOf(cell.getBooleanCellValue());

			case FORMULA:
				return cell.getCellFormula();

			default:
				return "";
		}
	}

	public static void logMemory() {
		logger.info("总内存：{}，已占有内存：{}，空闲内存：{}，最大内存：{}",
			formatSize(Runtime.getRuntime().totalMemory()),
			formatSize(Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory()),
			formatSize(Runtime.getRuntime().freeMemory()),
			formatSize(Runtime.getRuntime().maxMemory())
		);
	}

	private static String formatSize(Long size) {
		return size / 1024 / 1024 + "M";
	}
}
